package java.web.java_web_ch08;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;
import java.util.logging.Level;

public class BookManager {
    private static final Logger LOGGER = Logger.getLogger(BookManager.class.getName());

    public int addBook(String title, String author, int pages, int publishYear, String description, double price, String isbn, String publisher) throws SQLException {
        String sql = "INSERT INTO Books (BookTitle, BookAuthor, BookPages, BookPublishYear, BookDescription, BookPrice, BookISBN, BookPublisher) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        int rowsAffected = 0;
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, title);
            pstmt.setString(2, author);
            pstmt.setInt(3, pages);
            pstmt.setInt(4, publishYear);
            pstmt.setString(5, description);
            pstmt.setDouble(6, price);
            pstmt.setString(7, isbn);
            pstmt.setString(8, publisher);
            rowsAffected = pstmt.executeUpdate();
            LOGGER.log(Level.INFO, rowsAffected + " row(s) added.");
        }
        return rowsAffected;
    }

    public List<String> listBooks() throws SQLException {
        String sql = "SELECT * FROM Books";
        List<String> books = new ArrayList<>();
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                books.add("BookID: " + rs.getInt("BookID") + ", " +
                        "Title: " + rs.getString("BookTitle") + ", " +
                        "Author: " + rs.getString("BookAuthor") + ", " +
                        "Pages: " + rs.getInt("BookPages") + ", " +
                        "Publish Year: " + rs.getInt("BookPublishYear") + ", " +
                        "Description: " + rs.getString("BookDescription") + ", " +
                        "Price: $" + rs.getDouble("BookPrice") + ", " +
                        "ISBN: " + rs.getString("BookISBN") + ", " +
                        "Publisher: " + rs.getString("BookPublisher"));
            }
        }
        return books;
    }

    public int updateBook(int id, String title, String author, int pages, int publishYear, String description, double price, String isbn, String publisher) throws SQLException {
        String sql = "UPDATE Books SET BookTitle = ?, BookAuthor = ?, BookPages = ?, BookPublishYear = ?, BookDescription = ?, BookPrice = ?, BookISBN = ?, BookPublisher = ? WHERE BookID = ?";
        int rowsAffected = 0;
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, title);
            pstmt.setString(2, author);
            pstmt.setInt(3, pages);
            pstmt.setInt(4, publishYear);
            pstmt.setString(5, description);
            pstmt.setDouble(6, price);
            pstmt.setString(7, isbn);
            pstmt.setString(8, publisher);
            pstmt.setInt(9, id);
            rowsAffected = pstmt.executeUpdate();
            LOGGER.log(Level.INFO, rowsAffected + " row(s) updated.");
        }
        return rowsAffected;
    }

    public int deleteBook(int id) throws SQLException {
        String sql = "DELETE FROM Books WHERE BookID = ?";
        int rowsAffected = 0;
        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            rowsAffected = pstmt.executeUpdate();
            LOGGER.log(Level.INFO, rowsAffected + " row(s) deleted.");
        }
        return rowsAffected;
    }
}